<?php

require './vendor/autoload.php';

$config = array(
    "es_hosts" => "192.168.166.35:9200",
    "es_index" => "fkl_ceshi",
    "es_type" => "test_type",
    "mongo_hosts" => 'mongodb://192.168.112.17:27017',
    "mongo_database" => "fkl",
    "mongo_tables" => array("win_cdr"),
    "csv_path" => "1.csv",
    "vcc_id" => 2,
    'database' => array(
        'host'=>'localhost',
        'user'=>'root',
        'password'=>'',
        'port'=>'3306',
        'dbname'=>'ccod',
        'driver'=>'pdo_mysql',
    ),
);

//defaultLogge是一个快捷的方法；你也可以自己通过Monolog来创建自己的logger;
//$logger = \Elasticsearch\ClientBuilder::defaultLogger("./logs/test.log");
$logger = new \Monolog\Logger("test");
$logger->pushHandler(new \Monolog\Handler\StreamHandler("./logs/test.log", \Monolog\Logger::WARNING));
/** @var \Elasticsearch\Connections\Connection $esClient */
$esClient = \Elasticsearch\ClientBuilder::create()
    ->setHosts(array($config['es_hosts']))
    ->setLogger($logger)
    ->build();
//连接mongodb
$client = new MongoDB\Client($config['mongo_hosts']);
$mongoClient = $client->selectDatabase($config['mongo_database']);

//链接数据库
$conn = \Doctrine\DBAL\DriverManager::getConnection($config['database']);
$conn->executeQuery("set names utf8");
//打开文件
$csvFile = fopen($config['csv_path'],'r');
while ($csvData = fgetcsv($csvFile)) { //每次读取CSV里面的一行内容
    //过滤表头数据
    if(!is_numeric($csvData[1])) {
        continue;
    }
    $param = array(
        array(
            "match" => array("vcc_id" => $config['vcc_id'])
        ),
        array(
            "match" => array("start_time" => (int) strtotime($csvData[5]))
        ),
        array(
            "match" => array("caller" => (string) $csvData[1])
        )
    );
    exce($config, $param, $esClient, $mongoClient, $conn);
}

/**
 * @param $config
 * @param $param
 * @param \Elasticsearch\Client $esClient
 * @param \MongoDB\Client $mongoClient
 * @param $conn
 */
function exce($config, $param, $esClient, $mongoClient, $conn)
{
    //先查询 es
    $esCondition = array(
        'index' => $config["es_index"],
        'type' => $config["es_type"],
        'body'=>array(
            'query'=>array(
                "bool" => array(
                    "must" => $param
                )
            )
        )
    );
    $response = $esClient->search($esCondition);
    if (empty($response['hits']['hits'])) {
        return;
    }
    $sources = $response['hits']['hits'];
    $ids = array();
    $_ids = array();
    foreach ($sources as $source) {
        $ids[] = (int) $source['_source']['id'];
        $_ids[] = (string) $source['_id'];
        $dbCondition = array(
            "que_id" => empty($source['_source']['que_id']) ? 0 : $source['_source']['que_id'],
            "group_id" => empty($source['_source']['group_id']) ? 0 : $source['_source']['group_id'],
            "start_time" => empty($source['_source']['start_time']) ? 0 : $source['_source']['start_time'],
            "all_secs" => empty($source['_source']['all_secs']) ? 0 : $source['_source']['all_secs'],
            "server_num" => empty($source['_source']['server_num']) ? '' : $source['_source']['server_num'],
            "vcc_id" => $config['vcc_id'],
        );
        //删除 mysql 数据
        delReport($conn, $dbCondition);
    }
    $mgCondition = array(
        "_id" => array('$in' => $ids)
    );
    //循环删除所有mongodb表
    foreach ($config['mongo_tables'] as $val) {
        $col = $mongoClient->selectCollection($val);
        $col->deleteMany($mgCondition);
    }
    //删除es
    foreach ($_ids as $val) {
        $esCondition = array(
            'index' => $config["es_index"],
            'type' => $config["es_type"],
            "id" => $val,
        );
        $esClient->delete($esCondition);
    }
}

/**
 * @param \Doctrine\DBAL\Connection $conn
 * @param array $dbCondition
 */
function delReport($conn, $dbCondition)
{
    $allSql = array();
    $time = $dbCondition['all_secs'];//减去的时长;
    if (empty($dbCondition['start_time'])) {
        return;
    }
    $month = date("Y-m", $dbCondition['start_time']);
    $day   = date("Y-m-d", $dbCondition['start_time']);
    $hour  = date("h", $dbCondition['start_time']);
    $half  = $hour*2+(date("i", $dbCondition['start_time']) >= 30 ? 1 : 0);
    //X秒放弃量
    switch ($time) {
        case $time <= 10:
            $xstr = ', lost10_num = (lost10_num -1), lost20_num = (lost20_num -1), lost25_num = (lost25_num -1), lost30_num = (lost30_num -1), lost35_num = (lost35_num -1), lost40_num = (lost40_num - 1)';
            break;
        case $time <= 20:
            $xstr = ', lost20_num = (lost20_num -1), lost25_num = (lost25_num -1), lost30_num = (lost30_num -1), lost35_num = (lost35_num -1), lost40_num = (lost40_num - 1)';
            break;
        case $time <= 25:
            $xstr = ', lost25_num = (lost25_num -1), lost30_num = (lost30_num -1), lost35_num = (lost35_num -1), lost40_num = (lost40_num - 1)';
            break;
        case $time <= 30:
            $xstr = ', lost30_num = (lost30_num -1), lost35_num = (lost35_num -1), lost40_num = (lost40_num - 1)';
            break;
        case $time <= 35:
            $xstr = ', lost35_num = (lost35_num -1), lost40_num = (lost40_num - 1)';
            break;
        case $time <= 40:
            $xstr = ', lost40_num = (lost40_num - 1)';
            break;
        default:
            $xstr = '';
            break;
    }
    //技能组报表
    if (!empty($dbCondition['que_id'])) {
        $queSql = "UPDATE %s 
            SET in_num = (in_num - 1), lost_num = (lost_num - 1), lost_secs = (lost_secs - $time), queue_secs = (queue_secs - $time),ring_num=(ring_num - 1),
            lost1_num = (lost1_num -1), lost1_secs = (lost1_secs - $time) %s";

        $where = " where vcc_id=".$dbCondition['vcc_id']." and queue_id=".$dbCondition['que_id'];
        $monthSql = " and nowmonth='$month'";
        $daySql   = " and nowdate='$day'";
        $hourSql  = " and start_date='$day' and time_stamp=$hour";
        $halfSql  = " and start_date='$day' and time_stamp=$half";

        $allSql[] = sprintf($queSql, 'rep_queue_month', $xstr.$where.$monthSql);
        $allSql[] = sprintf($queSql, 'rep_queue_day', $xstr.$where.$daySql);
        $allSql[] = sprintf($queSql, 'rep_queue_hour', $xstr.$where.$hourSql);
        $allSql[] = sprintf($queSql, 'rep_queue_halfhour', $xstr.$where.$halfSql);
    }
    //呼叫中心报表
    $systemSql = "UPDATE %s 
    SET in_num = (in_num - 1), lost_num = (lost_num - 1), lost_secs = (lost_secs - $time),ring_num=(ring_num - 1),ring_secs=(ring_secs - $time),
    ivr_num = (ivr_num -1) %s";

    $where = " where vcc_id=".$dbCondition['vcc_id'];
    $monthSql = " and nowmonth='$month'";
    $daySql   = " and nowdate='$day'";
    $hourSql  = " and start_date='$day' and time_stamp=$hour";
    $halfSql  = " and start_date='$day' and time_stamp=$half";

    $allSql[] = sprintf($systemSql, 'rep_system_month', $xstr.$where.$monthSql);
    $allSql[] = sprintf($systemSql, 'rep_system_day', $xstr.$where.$daySql);
    $allSql[] = sprintf($systemSql, 'rep_system_hour', $xstr.$where.$hourSql);
    $allSql[] = sprintf($systemSql, 'rep_system_halfhour', $xstr.$where.$halfSql);

    //号码组报表
    if (!empty($dbCondition['server_num'])) {
        $phoneGroupSql = "UPDATE %s 
            SET in_num = (in_num - 1), lost_num = (lost_num - 1),ring_num=(ring_num - 1),
            ivr_num = (ivr_num -1), total_num = (total_num - 1) %s";

        $where = " where vcc_id=".$dbCondition['vcc_id']." and server_num=".$dbCondition['server_num'];
        $monthSql = " and nowmonth='$month'";
        $daySql   = " and nowdate='$day'";

        $allSql[] = sprintf($phoneGroupSql, 'rep_phone_group_month', $where.$monthSql);
        $allSql[] = sprintf($phoneGroupSql, 'rep_phone_group_day', $where.$daySql);
    }
    //业务组工作表现报表
    if (!empty($dbCondition['group_id'])) {
        $groupSql = "UPDATE %s 
            SET in_num = (in_num - 1) %s";

        $where    = " where vcc_id=".$dbCondition['vcc_id']." and group_id=".$dbCondition['group_id'];
        $monthSql = " and nowmonth='$month'";
        $daySql   = " and nowdate='$day'";
        $hourSql  = " and start_date='$day' and time_stamp=$hour";
        $halfSql  = " and start_date='$day' and time_stamp=$half";

        $allSql[] = sprintf($groupSql, 'rep_group_month', $where.$monthSql);
        $allSql[] = sprintf($groupSql, 'rep_group_day', $where.$daySql);
        $allSql[] = sprintf($groupSql, 'rep_group_hour', $where.$hourSql);
        $allSql[] = sprintf($groupSql, 'rep_group_halfhour', $where.$halfSql);
    }
    foreach ($allSql as $val) {
        $conn->exec($val);
    }
}
